import pymysql
import json

with open('./question_bank_all.json', 'r', encoding='utf-8') as fp:
    qb_list = json.load(fp)

conn = pymysql.connect(
    user='',  # TODO: your database username
    password='',  # TODO: your database password
    host='',  # TODO: your database server host
    port=3306,
    database=''  # TODO: your database name
)

conn.autocommit(True)
cursor = conn.cursor()

for idx, qb in enumerate(qb_list):
    title = qb['title']
    password = qb['password']

    check_sql = "select * from t_secure_question_bank where title='{0}'".format(title)
    cursor.execute(check_sql)

    # 去除重复题目
    if len(cursor.fetchall()) != 0:
        continue

    sql = "insert into t_secure_question_bank(id, title) values({0}, '{1}')".format(idx, title)
    cursor.execute(sql)
    
    if isinstance(password, list):
        for p in password:
            p_sql = "insert into t_secure_password(question_id, password) values({0}, {1})".format(idx, p)
            cursor.execute(p_sql)
    else:
        p_sql = "insert into t_secure_password(question_id, password) values({0}, {1})".format(idx, password)
        cursor.execute(p_sql)